import requests
from datetime import datetime
import psycopg2
from psycopg2 import Error
from time import sleep
import os


Postgresqlhost = os.environ.get('Postgresqlhost')
Postgresqldatabase = os.environ.get('Postgresqldatabase')
Postgresqluser = os.environ.get('Postgresqluser')
Postgresqlpassword = os.environ.get('Postgresqlpassword')

## 获取页面数据
def get_url_data(stock_code):
# 定义请求URL和请求体
  url = "https://wx.wind.com.cn/WindESGPublicWeb/Service/initroot"
  payload = {
      "Content": '{"Invokes":[{"Name":"CloudCommon","Parameters":{"CommandID":"Cloud:ESG:ESGDataCheckNew","WindCode":"%s"}},{"Name":"CloudCommon","Parameters":{"CommandID":"Cloud:BondESGAnalysis:ESGBasicInfo22","WindCode":"%s","CoverageType":"1,2","IndustryLvl":3}}]}' %(stock_code, stock_code)
  }

  # 发起POST请求
  response = requests.post(url, data=payload)

#   in_data_list = []
  # 检查请求是否成功
  if response.status_code == 200:
      # 解析响应数据
      data = response.json()

      current_date = datetime.now().date()

      in_data_dict = {
          "stock_code": data['Results'][1]['Value']['Object']['Data']['WindCode'][0],
          "company_name": data['Results'][1]['Value']['Object']['Data']['CompanyName'][0],
          "company_profile": data['Results'][1]['Value']['Object']['Data']['CompanyProfile'][0],
          "esg_synthesis_score": data['Results'][1]['Value']['Object']['Data']['Score'][0],
          "esg_management_score": data['Results'][1]['Value']['Object']['Data']['ManagementScore'][0],
          "esg_disputed_score": data['Results'][1]['Value']['Object']['Data']['EventScore'][0],
          "esg_industry_rank": data['Results'][1]['Value']['Object']['Data']['Ranking3'][0],
          "esg_industry_total_amount": data['Results'][1]['Value']['Object']['Data']['IndustryLevel3Count'][0],
          "esg_synthesis_score_fullmarks": 10,
          "esg_management_score_fullmarks": 7,
          "esg_disputed_score_fullmarks": 3,
          "esg_rating": data['Results'][1]['Value']['Object']['Data']['Rating'][0],
          "info_time": current_date,
          "esg_environment_score": data['Results'][1]['Value']['Object']['Data']['EScore'][0],
          "esg_environment_weight": data['Results'][1]['Value']['Object']['Data']['EWeight'][0],
          "esg_social_score": data['Results'][1]['Value']['Object']['Data']['SScore'][0],
          "esg_social_weight": data['Results'][1]['Value']['Object']['Data']['SWeight'][0],
          "esg_government_score": data['Results'][1]['Value']['Object']['Data']['GScore'][0],
          "esg_government_weight": data['Results'][1]['Value']['Object']['Data']['GWeight'][0],
          "company_industry3_code": data['Results'][1]['Value']['Object']['Data']['Industry3Code'][0],
          "company_industry_level3": data['Results'][1]['Value']['Object']['Data']['IndustryLevel3'][0],
          "company_industrylevel3_count": data['Results'][1]['Value']['Object']['Data']['IndustryLevel3Count'][0],
          "company_ranking3": data['Results'][1]['Value']['Object']['Data']['Ranking3'][0]
          }

    #   in_data_list.append(in_data_dict)
      print(data)
  else:
      print("Failed to make the request. Status code:", response.status_code)

  return in_data_dict


# 创建数据库连接和游标对象的函数
def create_connection():
    connection = psycopg2.connect(**connection_params)
    cursor = connection.cursor()
    return connection, cursor

# 执行查询函数
def execute_query(sql_sentence, query_type="query"):
    try:
        connection, cursor = create_connection()
        if query_type == "query":
          cursor.execute(sql_sentence)
          rows = cursor.fetchall()

          result = []
          for row in rows:
              row_dict = {cursor.description[i].name: value for i, value in enumerate(row)}
              result.append(row_dict)

        return result

    except (Exception, Error) as error:
        print("Error while connecting to PostgreSQL", error)

    finally:
        if connection:
            cursor.close()
            connection.close()

def update_company_infos(connection, cursor, data_list):
    # 构建更新语句
    update_query = "UPDATE company_info SET "
    update_query += ", ".join([f"{key} = %({key})s" for key in data_list[0].keys() if key != 'stock_code'])
    update_query += " WHERE stock_code = %(stock_code)s"

    # 执行批量更新操作
    cursor.executemany(update_query, data_list)

    # 提交事务
    connection.commit()

# 数据库连接参数
connection_params = {
    "host": Postgresqlhost,
    "database": Postgresqldatabase,
    "user": Postgresqluser,
    "password": Postgresqlpassword
}

# 示例查询和插入
query1 = "SELECT * FROM company_info"
result_table_info = execute_query(query1)
# print(result_web_info)

connection, cursor = create_connection()
company_info_list = []

keep_going = False
for result_table_info_one in result_table_info:

    # if result_table_info_one['stock_code'] == '301315.SZ':
    #     keep_going = True
    # if not keep_going:
    #     continue

    success = False
    while not success:
        try:
            company_info = get_url_data(result_table_info_one['stock_code'])
            success = True
        except Exception as e:
            print(f"An error occurred: {str(e)}. Retrying in 1 minute...")
            sleep(60)  # 等待一分钟

    # company_info_list.append(company_info)
    sleep(10)


# for company_info_list_one in company_info_list:
    sql = """
    UPDATE company_info SET 
    company_name = %(company_name)s, 
    company_profile = %(company_profile)s, 
    esg_synthesis_score = %(esg_synthesis_score)s, 
    esg_management_score = %(esg_management_score)s, 
    esg_disputed_score = %(esg_disputed_score)s, 
    esg_industry_rank = %(esg_industry_rank)s, 
    esg_industry_total_amount = %(esg_industry_total_amount)s, 
    esg_synthesis_score_fullmarks = %(esg_synthesis_score_fullmarks)s, 
    esg_management_score_fullmarks = %(esg_management_score_fullmarks)s, 
    esg_disputed_score_fullmarks = %(esg_disputed_score_fullmarks)s, 
    esg_rating = %(esg_rating)s, 
    info_time = %(info_time)s,
    esg_environment_score = %(esg_environment_score)s, 
    esg_environment_weight = %(esg_environment_weight)s, 
    esg_social_score = %(esg_social_score)s, 
    esg_social_weight = %(esg_social_weight)s, 
    esg_government_score = %(esg_government_score)s, 
    esg_government_weight = %(esg_government_weight)s, 
    company_industry3_code = %(company_industry3_code)s, 
    company_industry_level3 = %(company_industry_level3)s, 
    company_industrylevel3_count = %(company_industrylevel3_count)s, 
    company_ranking3 = %(company_ranking3)s
    WHERE stock_code = %(stock_code)s
    """

    for k,v in company_info.items():
        if k in ("esg_government_score", "esg_social_score", "esg_environment_score", "esg_disputed_score", "esg_management_score", "esg_synthesis_score") and v:
            # company_info[k] = round(v, 2)
            v_list = str(v).split('.')
            company_info[k] = float(v_list[0] + "." + v_list[1][:2])

    # cursor.execute(sql, company_info_list_one[0])
    cursor.execute(sql, company_info)

    connection.commit()
cursor.close()
connection.close()
